/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.Complaint;
import Entity.InfoLogin;
import Entity.PendingComp;
import Entity.ReportDetail;
import Entity.StaffDivision;
import java.util.Vector;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author h10t93
 */
public class AdminProcess {

    DataConnection connection = new DataConnection();
    MD5 md5 = new MD5();
    // fillter complaint not  handling

    public Vector<Complaint> fillterComp() {
        Vector<Complaint> list = new Vector<Complaint>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall("fillterComp");
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Complaint comp = new Complaint();
                comp.setComplaintID(rs.getInt(1));
                comp.setEmployeeID(rs.getInt(2));
                comp.setTitle(rs.getString(3));
                comp.setComplaintType(rs.getString(4));
                comp.setCompContent(rs.getString(5));
                comp.setDate(rs.getString(6));
                list.add(comp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    // hien thi danh sach staff de phan cong
    // select all staff to assignment

    public Vector<StaffDivision> staffDivision() {
        Vector<StaffDivision> list1 = new Vector<StaffDivision>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall("staffDivision");
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                StaffDivision sd = new StaffDivision();
                sd.setIdStaff(rs.getInt(1));
                sd.setName(rs.getString(2));
                sd.setDepartment(rs.getString(3));
                list1.add(sd);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list1;
    }
    // phan cong

    public boolean devision(int idStaff, int idComp, String priority) {
        int rs = 0;
        try {
            CallableStatement cs = connection.getConnection().prepareCall("divisionComp ?,?,?");
            cs.setInt(1, idStaff);
            cs.setInt(2, idComp);
            cs.setString(3, priority);
            rs = cs.executeUpdate();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs > 0;
    }
    // bao cao chi tiet khieu nai

    public Vector<ReportDetail> getReportDetail() {
        Vector<ReportDetail> list = new Vector<ReportDetail>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procReportDetail");
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                ReportDetail rp = new ReportDetail();
                rp.setIdUser(rs.getInt(1));
                rp.setDepartmentUser(rs.getString(2));
                rp.setNameUser(rs.getString(3));
                rp.setIdComplaint(rs.getInt(4));
                rp.setCompCategory(rs.getString(5));
                rp.setDateLodging(rs.getString(6));
                rp.setDateClose(rs.getString(7));
                rp.setNameTechnician(rs.getString(8));
                list.add(rp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    // bao cao khieu nai chi tiet theo ngay hoan thanh

    public Vector<ReportDetail> getReportDetailByDate(String date) {
        Vector<ReportDetail> list = new Vector<ReportDetail>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall(" procReportDetailByDate ?");
            cs.setString(1, date);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                ReportDetail rp = new ReportDetail();
                rp.setIdUser(rs.getInt(1));
                rp.setDepartmentUser(rs.getString(2));
                rp.setNameUser(rs.getString(3));
                rp.setIdComplaint(rs.getInt(4));
                rp.setCompCategory(rs.getString(5));
                rp.setDateLodging(rs.getString(6));
                rp.setDateClose(rs.getString(7));
                rp.setNameTechnician(rs.getString(8));
                list.add(rp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    // bao cao khieu nai chi tiet theo thang hoan thanh

    public Vector<ReportDetail> getReportDetailByMorth(String morth, String year) {
        Vector<ReportDetail> list = new Vector<ReportDetail>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall(" procReportDetailByMorth ?,?");
            cs.setString(1, morth);
            cs.setString(2, year);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                ReportDetail rp = new ReportDetail();
                rp.setIdUser(rs.getInt(1));
                rp.setDepartmentUser(rs.getString(2));
                rp.setNameUser(rs.getString(3));
                rp.setIdComplaint(rs.getInt(4));
                rp.setCompCategory(rs.getString(5));
                rp.setDateLodging(rs.getString(6));
                rp.setDateClose(rs.getString(7));
                rp.setNameTechnician(rs.getString(8));
                list.add(rp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    //report detail by year

    public Vector<ReportDetail> getReportDetailByYear(String year) {
        Vector<ReportDetail> list = new Vector<ReportDetail>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall(" procReportDetailByYear ?");
            cs.setString(1, year);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                ReportDetail rp = new ReportDetail();
                rp.setIdUser(rs.getInt(1));
                rp.setDepartmentUser(rs.getString(2));
                rp.setNameUser(rs.getString(3));
                rp.setIdComplaint(rs.getInt(4));
                rp.setCompCategory(rs.getString(5));
                rp.setDateLodging(rs.getString(6));
                rp.setDateClose(rs.getString(7));
                rp.setNameTechnician(rs.getString(8));
                list.add(rp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    //bao cao theo khieu nai chua duoc xu ly
    // pending complaints

    public Vector<PendingComp> getPendingComp() {
        Vector<PendingComp> pendingComps = new Vector<PendingComp>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procPendingComp");
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                PendingComp comp = new PendingComp();
                comp.setIdUser(rs.getInt(1));
                comp.setDepartment(rs.getString(2));
                comp.setNameUser(rs.getString(3));
                comp.setIdComp(rs.getInt(4));
                comp.setCompCategory(rs.getString(5));
                comp.setDateLodging(rs.getString(6));
                comp.setNameTechnician(rs.getString(7));
                pendingComps.add(comp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pendingComps;
    }
    // report pending complaints by day

    public Vector<PendingComp> getPendingCompByDate(String date) {
        Vector<PendingComp> pendingComps = new Vector<PendingComp>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procPendingCompByDate ?");
            cs.setString(1, date);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                PendingComp comp = new PendingComp();
                comp.setIdUser(rs.getInt(1));
                comp.setDepartment(rs.getString(2));
                comp.setNameUser(rs.getString(3));
                comp.setIdComp(rs.getInt(4));
                comp.setCompCategory(rs.getString(5));
                comp.setDateLodging(rs.getString(6));
                comp.setNameTechnician(rs.getString(7));
                pendingComps.add(comp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pendingComps;
    }
    // report complaints pending by morth

    public Vector<PendingComp> getPendingCompByMorth(String morth, String year) {
        Vector<PendingComp> pendingComps = new Vector<PendingComp>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procPendingCompByMorth ?,?");
            cs.setString(1, morth);
            cs.setString(2, year);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                PendingComp comp = new PendingComp();
                comp.setIdUser(rs.getInt(1));
                comp.setDepartment(rs.getString(2));
                comp.setNameUser(rs.getString(3));
                comp.setIdComp(rs.getInt(4));
                comp.setCompCategory(rs.getString(5));
                comp.setDateLodging(rs.getString(6));
                comp.setNameTechnician(rs.getString(7));
                pendingComps.add(comp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pendingComps;
    }
    // count pending complaints by year

    public Vector<PendingComp> getPendingCompByYear(String year) {
        Vector<PendingComp> pendingComps = new Vector<PendingComp>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procPendingCompByYear ?");
            cs.setString(1, year);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                PendingComp comp = new PendingComp();
                comp.setIdUser(rs.getInt(1));
                comp.setDepartment(rs.getString(2));
                comp.setNameUser(rs.getString(3));
                comp.setIdComp(rs.getInt(4));
                comp.setCompCategory(rs.getString(5));
                comp.setDateLodging(rs.getString(6));
                comp.setNameTechnician(rs.getString(7));
                pendingComps.add(comp);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pendingComps;
    }
    // count pending complaint by Morth

    public int getTotalCompPendingByMorth(String morth, String year) {
        int result = 0;
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procCountPendingCompByMorth ?,?");
            cs.setString(1, morth);
            cs.setString(2, year);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                result = rs.getInt(1);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    // count pending complaint by year

    public int getTotalCompPendingByYear(String year) {
        int result = 0;
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procCountPendingCompByYear ?");
            cs.setString(1, year);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                result = rs.getInt(1);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    // total complaints pending

    public int getTotalCompPending() {
        int result = 0;
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procCountPendingComp");
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                result = rs.getInt(1);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    // admin login

    public String adminLogin(String user, String pass) {
        String p = md5.encryptMD5(pass);
        String result=null;
        String sql = "SELECT *FROM tblStaff WHERE Staff_Username=? AND Staff_Password=? ";
        try {
            PreparedStatement pr = connection.getConnection().prepareCall(sql);
            pr.setString(1, user);
            pr.setString(2, p);
            ResultSet rs = pr.executeQuery();
            String permission;
            if (rs.next()) {
                permission = rs.getString(11);
                if (permission.equals("admin")) {
                    result ="admin";
                } else if (permission.equals("staff")) {
                    result ="staff";
                }
            }
            rs.close();
            pr.close();
            connection.disconnection();
        } catch (SQLException ex) {
            Logger.getLogger(AdminProcess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    // get info techniccian login

    public Vector<InfoLogin> getInfoLogin(String user) {
        Vector<InfoLogin> ils = new Vector<InfoLogin>();
        try {
            CallableStatement cs = connection.getConnection().prepareCall("procViewinfoLogin ?");
            cs.setString(1, user);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                InfoLogin il = new InfoLogin();
                il.setId(rs.getInt(1));
                il.setName(rs.getString(2));
                ils.add(il);
            }
            rs.close();
            cs.close();
            connection.disconnection();
        } catch (Exception e) {
        }
        return ils;
    }
}
